const express = require('express')
const router = express.Router()
const { db, genid } = require('../db/DbUtils')

//查询单篇博客
router.get('/detail', async (req, res) => {
  let { id } = req.query
  let detail_sql = 'SELECT * FROM `blog` WHERE `id` = ?'
  let { err, rows } = await db.async.all(detail_sql, [id])
  if (err == null) {
    res.send({
      code: 200,
      msg: '获取成功',
      rows,
    })
  } else {
    res.send({
      code: 500,
      msg: '添加失败',
      detail: err,
    })
  }
})

//添加博客
router.post('/_token/add', async (req, res) => {
  let { title, categoryId, content } = req.body
  //生成id
  let id = genid.NextId()
  // 利用时间戳生成创建时间
  let create_time = new Date().getTime()
  const insert_sql =
    'INSERT INTO `blog`(`id`,`title`,`category_id`,`content`,`create_time`) VALUES (?,?,?,?,?)'
  //整理参数
  let params = [id, title, categoryId, content, create_time]
  let { err, rows } = await db.async.run(insert_sql, params)
  if (err == null) {
    res.send({
      code: 200,
      msg: '添加成功',
    })
  } else {
    res.send({
      code: 500,
      msg: '添加失败',
      detail: err,
    })
  }
})

//修改博客
router.put('/_token/update', async (req, res) => {
  let { id, title, categoryId, content } = req.body
  const update_sql =
    'UPDATE `blog` SET `title` = ?,`content` = ?,`category_id` = ?  WHERE `id` = ?'
  //整理参数
  let params = [title, content, categoryId, id]
  let { err, rows } = await db.async.run(update_sql, params)
  if (err == null) {
    res.send({
      code: 200,
      msg: '修改成功',
    })
  } else {
    res.send({
      code: 500,
      msg: '修改失败',
      detail: err,
    })
  }
})

//删除博客
router.delete('/_token/delete', async (req, res) => {
  let id = req.query.id
  const delete_sql = 'DELETE FROM `blog` WHERE `id` = ?'
  let { err, rows } = await db.async.run(delete_sql, [id])
  if (err == null) {
    res.send({
      code: 200,
      msg: '删除成功',
    })
  } else {
    res.send({
      code: 500,
      msg: '删除失败',
      detail: err,
    })
  }
})

//查询博客
router.get('/search', async (req, res) => {
  /* 
        kewords:关键字
        categoryId:分类id
  
        分页：page：页码
            pageSize：分页大小
        */

  let { keywords, categoryId, page, pageSize } = req.query
  //如果没传page，为null，令其默认为1
  page = page == null ? 1 : page
  pageSize = pageSize == null ? 10 : pageSize
  categoryId = categoryId == null ? 0 : categoryId
  keywords = keywords == null ? '' : keywords
  //参数是拼接的
  let params = []
  //拼接whereSql语句
  let whereSqls = []
  if (categoryId != 0) {
    whereSqls.push(' `category_id` = ? ')
    params.push(categoryId)
  }
  if (keywords != '') {
    //用like关键字来模糊查询
    whereSqls.push(' (`title` LIKE ? OR `content` LIKE ?)')
    params.push('%' + keywords + '%')
    params.push('%' + keywords + '%')
  }
  let whereSqlStr = ''
  if (whereSqls.length > 0) {
    whereSqlStr = ' WHERE ' + whereSqls.join(' AND ')
  }
  // [ " `category_id` = ? ", "`title` LIKE ? OR `content` LIKE ?"]
  //完整的whereSql语句
  // WHERE `category_id` = ? AND `title` LIKE ? OR `content` LIKE ?

  //查询结果根据创建时间排序,limit子句用于限制查询结果返回的数量,【select * from tableName limit i,n 】
  //i为为查询结果的索引值（默认从0开始）
  //为为查询结果的索引值（默认从0开始）
  let searchSql =
    ' SELECT `id`,`category_id`,`create_time`,substr(`content`,0,50) AS `content` FROM `blog` ' +
    whereSqlStr +
    ' ORDER BY `create_time` DESC LIMIT ?,? '
  //传参 page,pagesize  1,0     2,10    3,5
  //从第0条开始，往后取10条记录     0,10    10,10   2,10
  //将计算后的参数与前面的参数合并
  let searchSqlParams = params.concat([(page - 1) * pageSize, pageSize])

  //计算总数,as关键字为结果取别名，方便取数据
  let searchCountSql = ' SELECT count(*) as `count` from `blog`' + whereSqlStr
  let searchCountParams = params

  //查分页数据
  let searchResult = await db.async.all(searchSql, searchSqlParams)
  // 查总数
  let countResult = await db.async.all(searchCountSql, searchCountParams)
  if (searchResult.err == null && countResult.err == null) {
    res.send({
      code: 200,
      msg: '查询成功',
      data: {
        keywords,
        categoryId,
        page,
        pageSize,
        rows: searchResult.rows,
        count: countResult.rows[0].count
      },
    })
  } else {
    res.send({
      code: 500,
      msg: '查询失败',
      detail: {
        searchResultErr: searchResult.err,
        countResultErr: countResult.err,
      },
    })
  }
})

module.exports = router
